package database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Map;
import java.util.Set;
import java.util.Vector;
import java.util.Map.Entry;

public class DB
{
//	String url = "jdbc:microsoft:sqlserver://" + strServerIPAddress + ":1433" +
//	";DatabaseName=" + strDatabaseName;
//	private static final String connectionName = "jdbc:odbc://localhost:1433;DatabaseName=forum";
	private static final String connectionName = "jdbc:odbc:forum";
	private static final String classForName = "sun.jdbc.odbc.JdbcOdbcDriver";


	public static enum userTable{blank,id,userName,userPassword,isAdmin,isActive};
	public static enum subForums{blank,subForumId,subForumTopic};
	public static enum subForumModerators{blank,subForumId,userId,complaintNumber,startDate};
	public static enum threads{blank,threadId,subForumId,ownerId,threadTitle,threadContent,creationDate};
	public static enum posts{blank,postsId,ownerId,threadId,postToPostId,postitle,postContent,creationDate};
	public static enum banndedUsers{blank,userId,subForumId,canBeBanned,isBanned};
	
	
	
    public static void execUpdate(String ex)
    {
    	try
		{
	    	Class.forName(classForName);
			Connection con  = DriverManager.getConnection(connectionName);
			Statement st=con.createStatement();
			st.executeUpdate(ex);
		}
		
		catch(Exception e)
		{
			System.out.println("connect fail");
		}
    }
    
    /**
     * a method that execute a query
     * @param ex - query to execute 
     * @return result set 
     */
    public static ResultSet execQuery(String ex)
    {
    	System.out.println("query is: " + ex);
    	ResultSet rs = null;
    	try
		{
	    	Class.forName(classForName);
			Connection con  = DriverManager.getConnection(connectionName);
			Statement st=con.createStatement();
			rs=st.executeQuery(ex);
			return rs;	
		}
		
		catch(Exception e)
		{
			System.out.println("connect fail");
		}
    	return rs;
    }
    
    public static int execCount(String ex)
    {
    	ResultSet rs = null;
    	try
		{
	    	Class.forName(classForName);
			Connection con  = DriverManager.getConnection(connectionName);
			Statement st=con.createStatement();
			rs=st.executeQuery(ex);
			return ResultSetSize(rs);
		}
    	catch (Exception e) {
			e.printStackTrace();
		}
    	return 0;
    }
    /**
     * return sub forum list 
     * @return result set
     */
	public static ResultSet getSubForumList() {
		return DB.execQuery("select * from subForums");
	}
	/**
     * return a thread's resultSet 
     * @return thread
     */
	public static ResultSet getThread(String threadId)
	{
		return DB.execQuery("select * from threads where threadId=" + threadId);
	}
	
	public static int ResultSetSize (ResultSet rs)
	{
		int counter=0;
		try {
			if(rs!=null)
			{
			while(rs.next()){
				counter++;                         
			   }
			
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	  return counter;	
		
		
	}
	/**
     * getter method for user roll by user name
     * @return user Roll 
     */
	public static String getUserRoll(String userName) {
		String query = "select * from users where userName='" +userName + "'";
		ResultSet rs2 = execQuery(query);
		int sizeOfQuery =  ResultSetSize(rs2);
		
		
		if(sizeOfQuery==0)
			return "guest";
		else
		{
			try {
				ResultSet rs  = execQuery(query);
				rs.next();
				if(rs.getBoolean(userTable.isAdmin.ordinal()))
					return "admin";
				else
					return "member";
			} catch (SQLException e) {
				e.printStackTrace();
			}
			return "";
		}
	}

	/**
     * getter method for user roll by user name and path
     * @return user Roll 
     */
	public static String getUserRoll(String userName, Vector<String> path) {
		String ans = getUserRoll(userName);
		int userId = getUserId(userName);
		if (userId >0 )
		{
			if (isModerator(userId,path.get(0)) && !ans.equals("admin"))
				ans = "moderator";
			if (path.size() ==2)
			{
				if (checkIfThreadOwner(userId,path))
				{
					ans = "owner";
				}
			}
			else if (path.size() ==3)
			{
				if (checkIfPostOwner(userId,path))
				{
					ans =  "owner";
				}
			}
			return ans;
		}
		return "guest";
	}
	/**
	 * a method that check if user id is the owner of the post
	 * @param userId user id
	 * @param path - where post is appear
	 * @return true if owns, otherwise false
	 */
	private static boolean checkIfPostOwner(int userId, Vector<String> path) {
		String query = "select * from posts where ownerId=" +userId + " and postId=" + path.get(2);
		return checkIfExsit(query);
	}

	/**
	 * a method that check if user id is the owner of the thread
	 * @param userId user id
	 * @param path - where thread is appear
	 * @return true if owns, otherwise false
	 */
	private static boolean checkIfThreadOwner(int userId,Vector<String> path) {
		String query = "select * from threads where ownerId=" +userId + " and threadId=" + path.get(1);
		return checkIfExsit(query);
	}

	private static boolean checkIfExsit(String query) {
		ResultSet res = execQuery(query);
		int sizeOfQuery =  ResultSetSize(res);
		if(sizeOfQuery==0)
			return false;
		else
			return true;
	}
/**
 * 
 * @param userId to check if is a moderator
 * @param subForumId to check if user id is moderator at 
 * @return true if user id is a moderator in sub forum id. otherwise  false
 */

	public static boolean isModerator(int userId, String subForumId) {
		String query = "select * from subForumModerators where userId=" +userId + " and subForumId=" + subForumId;
		return checkIfExsit(query);
	}

/**
 * authenitcation check
 * @param userName user name 
 * @param password
 * @return true if correct , otherwise false 
 */
	public static boolean authenticate(String userName, String password) {
		try
		{
	    	Class.forName(classForName);
			Connection con  = DriverManager.getConnection(connectionName);
			String selectStatement = "SELECT * FROM users WHERE userName = ?  and userPassword = ?";
			PreparedStatement prepStmt = con.prepareStatement(selectStatement);
			prepStmt.setString(1, userName.toLowerCase());
			prepStmt.setString(2, password);
			return checkIfExist(prepStmt);
		}
		catch (Exception e)
		{
			System.out.println("connect failed");
		}
		return false;
	}


	private static boolean checkIfExist(PreparedStatement prepStmt)
			throws SQLException {
		ResultSet rs = prepStmt.executeQuery();
		if (rs.next())
		{
			return true;
		}
		return false;
	}


/**
 * method that add a new member 
 * @param userName 
 * @param password
 * @return true if added, otherwise false
 */
	public static boolean AddNewMember(String userName, String password) {
		
			String userNamelowerCase = userName.toLowerCase();
			String query = "select * from users where userName='" +userNamelowerCase + "'";
			if(checkIfExsit(query))
				return false;
			else
			{
				String queryInsert = "Insert into users VALUES ('" + userNamelowerCase + "','" + password + "',0)";
				execQuery(queryInsert);
				return true;
			}
	}
	/**
	 * getter for user id
	 * @param userName
	 * @return user id of user name
	 */
	public static int getUserId (String userName) 
	{
		String query = "select userId from users where userName='" +userName + "'";
		ResultSet res = execQuery(query);
		try {
			while (res.next())
			{
				return res.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0;
		
		
		
		
	}

/**
 * check if sub forum exist 
 * @param subForumId to check 
 * @return true if exist , otherwise false
 */
	public static boolean isSubForumExist(String subForumId) {
		String query = "select * from subForums where subForumId=" +subForumId ;
		return checkIfExsit(query);
	}

/**
 * a method that add a thread message 
 * @param subForumId - sub forum to publish at
 * @param userId - publisher id
 * @param subject - title of thread
 * @param body - content of thread
 * @param date - publish date
 * @return int number represent id of the thread message
 */
	public static int addThreadMsg(String subForumId, int userId,String subject, String body, Date date) {
		String stringFromDate = getStringFromDate(date);
		String query = "insert into threads VALUES (" + subForumId +","+ Integer.toString(userId) + 
				",'" + subject + "','" + body + "','" + stringFromDate+"')";
		execUpdate(query);
		query = "select threadId from threads " +
			"where subForumId = "+ subForumId + " and ownerId = "+ userId  + 
			" and threadTitle = '" + subject  + "' and threadContent = '" + body + "' and creationDate = '" + stringFromDate + "'";
		int ans = getMsgId(query);
		return ans;
		
	}

	private static int getMsgId(String query) {
		ResultSet res = execQuery(query);
		int ans =0;
		try {
			if (res.next())
			{
				ans = res.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return ans;
	}
	/**
	 * convert date from date
	 * @param date 
	 * @return string date 
	 */
	public static String getStringFromDate(Date d)
	{
		String DATE_FORMAT = "yyyy-MM-dd HH:mm:ss";
	    SimpleDateFormat sdf =new SimpleDateFormat(DATE_FORMAT);
	    return sdf.format(d);
	}

/**
 * check if thread exist in db
 * @param thread to look for
 * @return true if exist otherwise false
 */
	public static boolean isThreadExist(String thread) {
		String query = "select * from threads where threadId=" +thread ;
		return checkIfExsit(query);
	}

/**
 * getter for post list 
 * @param thread  
 * @return result set of posts of thread 
 */
	public static ResultSet getPostList(String thread) {
		String query = "select * from posts where threadId=" + thread;
		return DB.execQuery(query);
	}
/**
 * getter for thread list 
 * @param subForumId that contains the thread list 
 * @return result set of threads list in subforum id
 */
	public static ResultSet getThreadsList(String subForumId) {
		String query = "select * from threads where subForumId=" + subForumId;
		return DB.execQuery(query);
	}

/**
 * check if userId exist 
 * @param userId 
 * @return true if exist otherwise false
 */
	public static boolean isUserExist(int userId) {
		return userId > 0;
	}

/**
 * check if post message exist 
 * @param postMsgId
 * @return true if exist otherwise false
 */
	public static boolean isPostMsgExist(String postMsgId) {
		String query = "select * from posts where postId=" +postMsgId;
		return checkIfExsit(query);
	}

/**
 * 
 * @param userId publisher
 * @param threadMsgId thread id 
 * @param responseToMsgId id of post to thread 
 * @param subject title
 * @param body content of message
 * @param date publish date 
 * @param isResponseToPost boolean represent if its response to other post 
 * @return int number represent of message id
 */
	public static int addPostMsg(int userId, String threadMsgId,String responseToMsgId, String subject, String body, Date date,
			boolean isResponseToPost) {
		String query = "insert into posts VALUES (" + userId +"," + threadMsgId+",";
		if (isResponseToPost)
		{
			query+=responseToMsgId+",";
		}
		else
		{
			query+="0,";
		}
		String stringFromDate = getStringFromDate(date);
		query+="'" + subject +"','" + body + "','" +stringFromDate+ "')";
		execUpdate(query);
		query = "select postId from posts " +
			"where ownerId = " + userId + " and threadId = " + threadMsgId + " and postTitle = '" + subject +  "' and postContent = '"
			 + body + "' and creationDate = '" + stringFromDate + "'";
		return getMsgId(query);

		
	}
/**
 * check if user name is administrator	
 * @param userName
 * @return true if is administrator otherwise false
 */
	public static boolean isAdmin(String userName)
	{
		return getUserRoll(userName).equals("admin");
	}

/**
 * check if user id can be moderator in sub forum 
 * @param subForumID to be moderator at
 * @param moderatorID user id
 * @return true if user id can be moderator in sub forum id
 */
	public static boolean canBeModerator(String subForumID, int moderatorID) {
		String query = "select * from subForumModerators where subForumId=" +subForumID 
			+" and userId=" + moderatorID;
		if (!checkIfExsit(query) /*&& isLegalModeartor(subForumID,moderatorID)*/) //only check this when giving candidates
		{
			return true;
		}
		return false;
	}

/**
 * check if moderator is legal in subforum id
 * @param subForumID
 * @param moderatorID
 * @return true if legal otherwise false
 */
	public static boolean isLegalModeartor(String subForumID, int moderatorID) {
		String query = "SELECT COUNT (threadId) FROM threads WHERE subForumId=" + subForumID + "and ownerId=" + moderatorID;
		ResultSet res  =DB.execQuery(query);
		int counter = 0;
		try
		{
			if (res.next())
			{
				counter +=res.getInt(1);
			}
			if (counter > 4)
			{
				return true;
			}
			else
			{
				query = "SELECT COUNT(*) AS numOfPosts FROM posts INNER JOIN users ON posts.ownerId = users.userId INNER JOIN" +
                      "threads ON posts.threadId = threads.threadId AND users.userId = threads.ownerId INNER JOIN" +
                      "subForums ON threads.subForumId = subForums.subForumId" +
                      "where  threads.ownerId=" + moderatorID +" and  subForums.subForumId= " + subForumID;
				res = DB.execQuery(query);
				if (res.next())
				{
					counter +=res.getInt(1);
				}
				if (counter > 4)
				{
					return true;
				}
				
			}
		}
		catch (SQLException e)
		{
			System.out.println("isLegalModeartor SQLException " + e.getMessage());
		}
		return false;
	}

/**
 * add moderator id to sub forum id
 * @param subForumID
 * @param moderatorID
 */
	public static void addModerator(String subForumID, int moderatorID) {
		String query = "insert into subForumModerators VALUES (" + subForumID +"," + moderatorID+",0,'" +
		 getStringFromDate(new Date(System.currentTimeMillis())) + "')";
		execUpdate(query);
	}
	/**
	 * return candidate list 
	 * @param subForumID
	 * @return set of candidate list 
	 */
	public static Set<String> getCandidateList(String subForumID)
	{
		Map<String,Integer> candidateList = new HashMap<String,Integer>();
		try 
		{
			
			String query = getQueryForNumOfThreadMsg(subForumID);
			ResultSet res =execQuery(query);
			putCandidateInMap(candidateList,res);
			query = getQueryForNumOfPostMsg(subForumID);
			res =execQuery(query);
			putCandidateInMap(candidateList,res);
		}
		catch (SQLException e)
		{
			System.out.println("in getCandidateList SQLException " + e.getMessage());
		}
		return getCandidateFromMap(candidateList);
	}
/**
 * an helper function - get candidate list from hash map of users
 * @param candidateList hash map of candidate list 
 * @return candidate list from hash map 
 */

	private static Set<String> getCandidateFromMap(
			Map<String, Integer> candidateList) {
		Set<String> ans = new HashSet<String>();
		for (Entry<String, Integer> entry : candidateList.entrySet()) {
			if (entry.getValue() >4)
			{
				ans.add(entry.getKey());
			}
		}
		return ans;
	}
/**
 * get query of user + number of post message 
 * @param subForumID to look for at 
 * @return string represent query of user + number of post message
 */
	private static String getQueryForNumOfPostMsg(String subForumID) {
		String query = "SELECT users.userName, COUNT(*) AS numberOfmessages FROM posts INNER JOIN " +
        	"threads ON posts.threadId = threads.threadId INNER JOIN users ON posts.ownerId = users.userId" +
        	" AND threads.ownerId = users.userId WHERE (threads.subForumId = " + subForumID + ")" +
        	"GROUP BY users.userName";
		return query;
	}

	/**
	 * get query of user + number of thread message 
	 * @param subForumID to look for at 
	 * @return string represent query of user + number of thread message
	 */
	private static String getQueryForNumOfThreadMsg(String subForumID) {
		String query = "SELECT     users.userName, COUNT(*) AS numberOfmessages FROM threads INNER JOIN users ON threads.ownerId =" +
				" users.userId where threads.subForumId =" + subForumID + "GROUP BY users.userName";
		return query;
	}

/**
 * assign candidate in hash map of candidates
 * @param candidateList list of candidates
 * @param res 
 * @throws SQLException
 */
	private static void putCandidateInMap(Map<String, Integer> candidateList,
			ResultSet res) throws SQLException{
		while (res.next())
		{
			String userName = res.getString(1);
			int numOfMsg = res.getInt(2);
			if (candidateList.containsKey(userName))
			{
				int oldNum = candidateList.get(userName);
				int newNumOfmsgs = numOfMsg + oldNum;
				candidateList.put(userName, newNumOfmsgs);
			}
			else
			{
				candidateList.put(userName, numOfMsg);
			}
		}
	}

/**
 * getter for moderator list in a sub forum
 * @param subForumId 
 * @return result set of moderator list 
 */
	public static ResultSet getModeratorList(String subForumId) {
		String query = "SELECT users.userName FROM subForumModerators INNER JOIN " +
                      "users ON subForumModerators.userId = users.userId " +
                      "WHERE (subForumModerators.subForumId = "+subForumId +" )";
		return execQuery(query);
	}

/**
 * check if moderator publish message in the last hour
 * @param subForumId 
 * @param moderatorID
 * @return true if didn't publish in last hour otherwise false
 */
	public static boolean notPublishMsgInLastHour(String subForumId, int moderatorID) {
		long hour = 1000*60*60;
		long hourAgo = System.currentTimeMillis() - hour;
		String hourFromNowString = getStringFromDate(new Date(hourAgo));
		String query = "select * from threads " +
						"where subForumId = " + subForumId +" and ownerId = " + moderatorID +
						" and creationDate > '" + hourFromNowString + "'";
		if (!checkIfExsit(query))
		{
			query = "SELECT * " +
					"FROM posts INNER JOIN threads ON posts.threadId = threads.threadId " +
					"where threads.subForumId = " +1 + " and posts.ownerId = " + moderatorID +" and posts.creationDate > '" +
					hourFromNowString + "'";
			if (!checkIfExsit(query))
			{
				return true;
			}
		}
		return false;
	}

/**
 * remove moderator 
 * @param subForumId to remove from 
 * @param moderatorID to remove 
 */
	public static void removeModerator(String subForumId, int moderatorID) {
		String query = "delete from subForumModerators where userId = " + moderatorID +" and subForumId = " + subForumId;
		execUpdate(query);
	}

/**
 * add sub forum 
 * @param subForumName
 * @param userID operator
 * @return string represent id of the added sub forum
 * @throws SQLException
 */
	public static String addSubForum(String subForumName, int userID) throws SQLException{
		String query = "insert into subForums VALUES ('" + subForumName +"')";
		execUpdate(query);
		query = "select * from subForums where subForumTopic = '" + subForumName + "'";
		ResultSet res = execQuery(query);
		String ans ="";
		if (res.next())
		{
			ans = res.getString(subForums.subForumId.ordinal());
		}
		addModerator(ans, userID);
		return ans;
	}

/**
 * getter of all users 
 * @return result set of all users
 */
	public static ResultSet getAllUsers() {
		String query = "select * from users";
		return execQuery(query);
	}

/**
 * check if sub forum exist 
 * @param subForumName to search 
 * @return true if exist otherwise false
 */
	public static boolean isSubForumExistBySubForumName(String subForumName) {
		String query = "select * from subForums where subForumTopic = '" + subForumName +"'";
		return checkIfExsit(query);
	}

/**
 * delete sub forum 
 * @param subForumId to delete
 */
	public static void deleteSubForum(String subForumId) {
		deleteAllModeratorsFromSubForum(subForumId);
		deleteAllPostsFromSubForum(subForumId);
		deleteAllThreadsFromSubForum(subForumId);
		deleteAllBannedUsers(subForumId);
		String query = "delete from subForums where subForumId = " + subForumId;
		execUpdate(query);
	}

/**
 * delete all banned users
 * @param subForumId to delete banned user from 
 */
	private static void deleteAllBannedUsers(String subForumId) {
		String query = "delete from banndedUsers where subForumId = " + subForumId;
		execUpdate(query);
	}

/**
 * delete thread message from a sub forum 
 * @param subForumId to delete from 
 */
	public static void deleteAllThreadsFromSubForum(String subForumId) {
		String query = "delete from threads where subForumId = " + subForumId;
		execUpdate(query);
	}

/**
* delete posts message from a sub forum 
 * @param subForumId to delete from 
 */
	public static void deleteAllPostsFromSubForum(String subForumId) {
		String query = "delete a FROM posts as a INNER JOIN threads as b ON a.threadId = b.threadId " +
						"where b.subForumId = " + subForumId;
		execUpdate(query);
		
	}

	/**
	* delete moderators  from a sub forum 
	 * @param subForumId to delete from 
	 */
	public static void deleteAllModeratorsFromSubForum(String subForumId) {
		String query = "delete from subForumModerators where subForumId = " + subForumId;
		execUpdate(query);
	}
	/**
	 * getter for number of messages in a sub forum 
	 * @param subForumId to search at 
	 * @return number of messages in sub forum 
	 */
	public static int getNumberOfMessagesInSubForum(String subForumId) {
		int ans = 0;
		try
		{
			String query = "SELECT count (*) as numOFmsgs FROM threads "+
							"WHERE subForumId  = " +subForumId;
			ResultSet res = execQuery(query);
			if (res.next())
			{
				ans = res.getInt(1);
			}
			query = "SELECT  count (*) as numOfposts FROM posts INNER JOIN threads" +
					" ON posts.threadId = threads.threadId" +
					" where threads.subForumid = " +subForumId;
			res =  execQuery(query);
			if (res.next())
			{
				ans += res.getInt(1);
			}
		}
			
		catch (SQLException e)
		{
			System.out.println("getNumberOfMessagesInSubForum SQLEXCEPTION "+e.getMessage());
		}
		return ans;
	}

/**
 * edit thread message 
 * @param threadId to edit
 * @param newSubject new title 
 * @param newBody new content 
 */
	public static void editThreadMsg(String threadId, String newSubject,
			String newBody) {
		String query = "update threads "+
						"set threadTitle = '" + newSubject + "' ,threadContent = '" + newBody + "' " +
						"where threadId = " + threadId;
		execUpdate(query);
	}


	public static void editPostMsg(String messageId, String newSubject,
			String newBody) {
		String query = "update posts "+
						"set postTitle = '" + newSubject + "' ,postContent = '" + newBody + "' " +
						"where postId = " + messageId;
		execUpdate(query);
		
	}

/**
 * check if user exist in sub forum more than a month 
 * @param userName 
 * @param subForumId
 * @return true if user exist more than a month in sub forum otherwise false
 */
	public static boolean checkMoreThanAmonth(String userName, String subForumId) {
		long monthTime = 1000*60*60*24*30;
		Date monthAgo = new Date(System.currentTimeMillis() -monthTime);
		int userId = getUserId(userName);
		String query = "select * from subForumModerators where userId = " + userId +
						" and subForumId = "+ subForumId +" and startDate < '" + getStringFromDate(monthAgo) +"'";
		return checkIfExsit(query);
	}
/**
 * getter of number of messages per user 
 * @param selectedUser
 * @return number of message per user 
 */
	public static int getMessagePerUserSelected(String selectedUser) {
		int ans = 0;
		int id = getUserId(selectedUser);
		try {
			String query = "SELECT count(*) as numOfmsgsPerUser FROM threads "+
							"WHERE ownerId ="+id;
			ResultSet res = execQuery(query);
			if (res.next())
				ans = res.getInt(1);
			query = "SELECT count (*) as numOfmsgsPerUser FROM posts "+
					"WHERE ownerId = "+id;
			res = execQuery(query);
			if (res.next())
				ans+= res.getInt(1);
		} catch (SQLException e) {
			System.out.println(" getMessagePerUserSelected SQLException "+e.getMessage());
		}
		return ans;
	}

	/**
	 * delete post message 
	 * @param messageId to delete
	 */
	public static void deletePostMessage(String messageId) {
		try
		{
			String pre = "select * from posts WHERE postToPostId = "+messageId;
			ResultSet res = execQuery(pre);
			while(res.next())
			{
				deletePostMessage(res.getString(posts.postsId.ordinal()));
			}
			String query = "delete from posts WHERE postId = "+messageId;
			execUpdate(query);
		}
		catch (SQLException e) {
			System.out.println("deletePostMessage SQLException " + e.getMessage());
		}
	
	}

/**
 * delete thread message 
 * @param threadId to delete 
 */
	public static void deleteThreadMessage(String threadId) {
		String pre = "delete from posts WHERE threadId = "+threadId;
		execUpdate(pre);
		String query = "delete from threads WHERE threadId= "+threadId;
		execUpdate(query);
	}

/**\
 * getter for all candidates to be an administrator
 * @return
 */
	public static ResultSet getAdminCandidates() {
		return getAllUsers(); // for now every body can be admin
	}

/**
 * check if user exist in database
 * @param userName 
 * @return true  if exist otherwise false
 */
	public static boolean isUserExsit(String userName) {
		
		return isUserExist(getUserId(userName));
	}

/**
 * a method to cahne administrator
 * @param userName old administrator
 * @param newAdmin a new administrator
 */
	public static void changeAdmin(String userName, String newAdmin) {
		String query = "update users set isAdmin = 1 where userName = '" + newAdmin + "'";
		execUpdate(query);
		query = "update users set isAdmin = 0 where userName = '" + userName + "'";
		execUpdate(query);
	}

/**
 * a getter for all users that published threads in a sub forum 
 * @param subForumId
 * @return hash map of all users that published threads in a sub forum 
 */
	public static Map<String,Boolean> getAllWritersInSubForum(int subForumId) {
		Map<String,Boolean> ans = new HashMap<String, Boolean>();
		putUsersInMap(subForumId, ans,getThreadsList(Integer.toString(subForumId)),threads.ownerId.ordinal());
		putUsersInMap(subForumId, ans, getAllUsersThatWritePostsInSubForum(subForumId), 1);
		return ans;
	}

	/**
	 * a getter for all users that published posts in a sub forum 
	 * @param subForumId
	 * @return hash map of all users that published posts in a sub forum 
	 */
	private static ResultSet getAllUsersThatWritePostsInSubForum(int subForumId) {
		String query = "SELECT posts.ownerId " +
				"FROM  posts INNER JOIN threads ON posts.threadId = threads.threadId " +
				"WHERE (threads.subForumId = " + subForumId + " ) " +
				"GROUP BY posts.ownerId";
		return execQuery(query);
	}


	private static void putUsersInMap(int subForumId,
			Map<String, Boolean> ans,ResultSet res,int tableIndex) {
		Map<Integer,Boolean> checkIfexsistInMap = new HashMap<Integer, Boolean>();
		try {
			while (res.next())
			{
				int userId = res.getInt(tableIndex);
				if (!checkIfexsistInMap.containsKey(userId))
				{
					String userName= getUserName(userId);
					ans.put(userName, true);
					checkIfexsistInMap.put(userId, true);
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.out.println("SQLException in getAllWritersInSubForum "+ e.getMessage());
		}
	}

/**
 * a getter for user name
 * @param userId
 * @return user name of user id
 * @throws SQLException
 */
	public static String getUserName(int userId) throws SQLException {
		String query = "select userName from users where userId = " + userId;
		ResultSet res = execQuery(query);
		String ans = null;
		if (res.next())
		{
			ans = res.getString(1);
		}
		return ans;
	}


	private static void addAllPostWriters(Map<String, Boolean> ans, int threadId) 
	{
		String query = "SELECT users.userName " +
				"FROM posts INNER JOIN users ON posts.ownerId = users.userId " +
				"WHERE (posts.threadId = " + threadId + " ) " +
						"GROUP BY users.userName";
		ResultSet res = execQuery(query);
		try {
			while (res.next())
			{
				ans.put(res.getString(1), true);
			}
		} catch (SQLException e) {
			System.out.println("SQLException in addAllPostWriters " + e.getMessage());
		}
		
	}


	private static String getThreadOwner(int threadId) {
		String query = "SELECT users.userName FROM threads INNER JOIN " +
        				"users ON threads.ownerId = users.userId " +
        				"WHERE (threads.threadId = "+ threadId +") ";
		ResultSet res = execQuery(query);
		String ans = null; 
		try {
			if (res.next())
			{
				ans = res.getString(1);
			}
		} catch (SQLException e) {
			System.out.println("SQLException in getThreadOwner " + e.getMessage());
		}
		return ans;
	}



	
	
	
	/**
	 * check if user is active
	 * @param userName
	 * @return true if active , else false
	 */
	public static boolean isActiveUser(String userName) {
		boolean ans = false;
		String query = "SELECT Active from users " +
					   	" WHERE users.userName ='"+ userName + "'";
		ResultSet res = execQuery(query);
		try {
			if (res.next())
			{
				ans = res.getBoolean(1);
			}
		} catch (SQLException e) {
			System.out.println("SQLException in isActiveUser " + e.getMessage());
		}
		return ans;
		
	}

/**
 * change state of user (active, inactive)
 * @param userName 
 * @param mode
 */
	public static void changeMode(String userName, int mode) {
		String query = "update users "+
						"set Active = " +mode +
						" where users.userName = '" +userName + "'";
		System.out.println(query);
		execUpdate(query);
	}

	
	/**
	 * change state to banned 
	 * @param userId
	 * @param isActive 
	 */
	public static void changeStatetoBanned(int userId, int isActive) {
		String query = "update banndedUsers "+
				"set isBanned = " +isActive +
				" where banndedUsers.userId = " +userId;
		execUpdate(query);
		
	}

	/**
	 * change state to un banned 
	 * @param userId
	 * @param isActive 
	 */
	public static void changeStatetoUNBanned(int userId, int isActive) {
		String exec = "delete * from banndedUsers WHERE banndedUsers.userId = "+userId;
		execQuery(exec);
		
	}

/**
 * getter for all optional bannded users
 * @param userName
 * @param subForum
 * @return result set of all optional bannded users 
 */
	public static ResultSet getoptionalBanndedUsers(String userName, String subForum) {
		int SubId = Integer.parseInt(subForum);
		String query  = "SELECT userName from banndedUsers  join users on bannededUsers.userId = users.userId"+
						"WHERE banndedUsers.subForumId = "+SubId +" AND banndedUsers.canBeBanned = 1";
		ResultSet result =execQuery(query);
		return result;
	}

/**
 * check if user bannded from sub forum 
 * @param userId to be checked
 * @param subForumId
 * @return true if bannded otherwise false
 */
	public static boolean isUserBannedFromSubForum(int userId, String subForumId) {
		String query ="select * from banndedUsers where userId = " + userId + " and subForumId = " + subForumId
						+ " and IsBanned = 1";
		return checkIfExsit(query);
	}

	public static boolean isRowExistBannedUsers(int userId, String subForumId) {
		String query ="select * from banndedUsers where userId = " + userId + " and subForumId = " + subForumId;
		return checkIfExsit(query);
	}

/**
 * add bannded user to list  
 * @param userId to add 
 * @param subForumId
 */
	public static void addBannedUsers(int userId, String subForumId) {
		String query = "insert into banndedUsers VALUES (" + userId +"," + subForumId+"," + "1,0)";
		execUpdate(query);
	}


	
	
}